--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_PRO_DEPF.HQL
--    Functions : 表39：存款发生额分产品统计表 
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-12  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_PRO_DEPF PARTITION (DATA_DATE = '#V_DATA_DATE#')
SELECT  AREA.AREA_CODE_4                      AS FIN_ORG_DIST
       ,XT.CCY                                AS CCY
       ,XT.DEPOSIT_TYPE                       AS DEPOSIT_TYPE    -- 产品类别
       ,XT.TYPE_DESC                          AS TYPE_DESC       -- 产品类别描述
       ,SUM(XT.OCCUR_AMOUNT )/100000000       AS OCCUR_AMOUNT    -- 存款发生额
       ,SUM(XT.AMOUNT_LM     )/100000000      AS AMOUNT_LM       -- 发生额上期末
       ,SUM(XT.AMOUNT_YS     )/100000000      AS AMOUNT_YS       -- 当年累计发生额
       ,SUM(XT.AMOUNT_GROW   )/100000000      AS AMOUNT_GROW     -- 同比增长
       ,SUM(XT.WSUM_BAL   )/100000000         AS WSUM_BAL        -- 发生额加权值
       ,SUM(XT.WAMOUNT_LM    )/100000000      AS WAMOUNT_LM      -- 发生额加权值上期末
       ,SUM(XT.WAMOUNT_YS    )/100000000      AS WAMOUNT_YS      -- 当年累计发生额加权值
       ,SUM(XT.WAMOUNT_LC    )/100000000      AS WAMOUNT_LC      -- 发生额加权值去年同期
FROM (
    -- 个人
    SELECT
         DEPF.FIN_ORG_NO                                              AS FIN_ORG_NO
        ,DEPF.CCY                                                     AS CCY
        ,'1'                                                          AS DEPOSIT_TYPE       -- 产品类别
        ,'个人'                                                       AS TYPE_DESC          -- 产品类别描述
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)                               AS OCCUR_AMOUNT       -- 存款发生额
        ,COALESCE(DEPF.AMOUNT_LM,0)                                   AS AMOUNT_LM          -- 发生额上期末
        ,COALESCE(DEPF.AMOUNT_YS,0)                                   AS AMOUNT_YS         -- 当年累计发生额
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)-COALESCE(DEPF.AMOUNT_LC,0)    AS AMOUNT_GROW        -- 同比增长
        ,COALESCE(DEPF.WSUM_BAL,0)                                    AS WSUM_BAL           -- 发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LM,0)                                  AS WAMOUNT_LM         -- 发生额加权值上期末
        ,COALESCE(DEPF.WAMOUNT_YS,0)                                  AS WAMOUNT_YS        -- 当年累计发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LC,0)                                  AS WAMOUNT_LC         -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D013','D014','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069')AND CLIENT_TYPE = '1')DEPF

    UNION ALL

    -- 个人_活期储蓄存款+定期储蓄存款+定活两便存款+通知存款+协议存款+协定存款+保证金存款
    SELECT
         DEPF.FIN_ORG_NO                                                                                                        AS FIN_ORG_NO
        ,DEPF.CCY                                                                                                               AS CCY
        ,CASE WHEN DEPF.DEPOSIT_TYPE='D013' THEN '11'                                                                          
              WHEN DEPF.DEPOSIT_TYPE='D014' THEN '12'                                                                          
              WHEN DEPF.DEPOSIT_TYPE='D02'  THEN '13'                                                                          
              WHEN DEPF.DEPOSIT_TYPE='D03'  THEN '14'                                                                          
              WHEN DEPF.DEPOSIT_TYPE='D04'  THEN '15'                                                                          
              WHEN DEPF.DEPOSIT_TYPE IN ('D051','D052')  THEN '16'                                                             
              WHEN DEPF.DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069')  THEN '17'            
         END                                                                                                                    AS DEPOSIT_TYPE
        ,CASE WHEN DEPF.DEPOSIT_TYPE='D013' THEN '活期储蓄存款'
              WHEN DEPF.DEPOSIT_TYPE='D014' THEN '定期储蓄存款'
              WHEN DEPF.DEPOSIT_TYPE='D02'  THEN '定活两便存款'
              WHEN DEPF.DEPOSIT_TYPE='D03'  THEN '通知存款'
              WHEN DEPF.DEPOSIT_TYPE='D04'  THEN '协议存款'
              WHEN DEPF.DEPOSIT_TYPE IN ('D051','D052')  THEN '协定存款'
              WHEN DEPF.DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069')  THEN '保证金存款'
         END                                                                                                                    AS TYPE_DESC
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)                                                                                         AS OCCUR_AMOUNT      -- 存款发生额
        ,COALESCE(DEPF.AMOUNT_LM,0)                                                                                             AS AMOUNT_LM          -- 发生额上期末
        ,COALESCE(DEPF.AMOUNT_YS,0)                                                                                             AS AMOUNT_YS          -- 当年累计发生额
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)-COALESCE(DEPF.AMOUNT_LC,0)                                                              AS AMOUNT_GROW        -- 同比增长
        ,COALESCE(DEPF.WSUM_BAL,0)                                                                                              AS WSUM_BAL        -- 发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LM,0)                                                                                            AS WAMOUNT_LM         -- 发生额加权值上期末
        ,COALESCE(DEPF.WAMOUNT_YS,0)                                                                                            AS WAMOUNT_YS         -- 当年累计发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LC,0)                                                                                            AS WAMOUNT_LC         -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D013','D014','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069') AND CLIENT_TYPE = '1')DEPF

    UNION ALL

    -- 机构
    SELECT
         DEPF.FIN_ORG_NO                                             AS FIN_ORG_NO
        ,DEPF.CCY                                                    AS CCY
        ,'0'                                                         AS DEPOSIT_TYPE      -- 产品类别
        ,'机构'                                                      AS TYPE_DESC          -- 产品类别描述
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)                              AS OCCUR_AMOUNT       -- 存款发生额
        ,COALESCE(DEPF.AMOUNT_LM,0)                                  AS AMOUNT_LM          -- 发生额上期末
        ,COALESCE(DEPF.AMOUNT_YS,0)                                  AS AMOUNT_YS          -- 当年累计发生额
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)-COALESCE(DEPF.AMOUNT_LC,0)   AS AMOUNT_GROW        -- 同比增长
        ,COALESCE(DEPF.WSUM_BAL,0)                                   AS WSUM_BAL           -- 发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LM,0)                                 AS WAMOUNT_LM         -- 发生额加权值上期末
        ,COALESCE(DEPF.WAMOUNT_YS,0)                                 AS WAMOUNT_YS         -- 当年累计发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LC,0)                                 AS WAMOUNT_LC         -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D011','D012','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069') AND CLIENT_TYPE = '0')DEPF

    UNION ALL

    -- 机构_活期储蓄存款+定期储蓄存款+定活两便存款+通知存款+协议存款+协定存款+保证金存款
    SELECT
         DEPF.FIN_ORG_NO                                                                                                     AS FIN_ORG_NO
        ,DEPF.CCY                                                                                                            AS CCY
        ,CASE WHEN DEPF.DEPOSIT_TYPE='D011' THEN '21'
              WHEN DEPF.DEPOSIT_TYPE='D012' THEN '22'
              WHEN DEPF.DEPOSIT_TYPE='D02'  THEN '23'
              WHEN DEPF.DEPOSIT_TYPE='D03'  THEN '24'
              WHEN DEPF.DEPOSIT_TYPE='D04'  THEN '25'
              WHEN DEPF.DEPOSIT_TYPE IN ('D051','D052')  THEN '26'
              WHEN DEPF.DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069')  THEN '27'
         END                                                                                                                 AS DEPOSIT_TYPE
        ,CASE WHEN DEPF.DEPOSIT_TYPE='D011' THEN '活期储蓄存款'
              WHEN DEPF.DEPOSIT_TYPE='D012' THEN '定期储蓄存款'
              WHEN DEPF.DEPOSIT_TYPE='D02'  THEN '定活两便存款'
              WHEN DEPF.DEPOSIT_TYPE='D03'  THEN '通知存款'
              WHEN DEPF.DEPOSIT_TYPE='D04'  THEN '协议存款'
              WHEN DEPF.DEPOSIT_TYPE IN ('D051','D052')  THEN '协定存款'
              WHEN DEPF.DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069')  THEN '保证金存款'
         END                                                                                                                 AS TYPE_DESC
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)                                                                                      AS OCCUR_AMOUNT      -- 存款发生额
        ,COALESCE(DEPF.AMOUNT_LM,0)                                                                                          AS AMOUNT_LM          -- 发生额上期末
        ,COALESCE(DEPF.AMOUNT_YS,0)                                                                                          AS AMOUNT_YS          -- 当年累计发生额
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)-COALESCE(DEPF.AMOUNT_LC,0)                                                           AS AMOUNT_GROW        -- 同比增长
        ,COALESCE(DEPF.WSUM_BAL,0)                                                                                           AS WSUM_BAL        -- 发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LM,0)                                                                                         AS WAMOUNT_LM         -- 发生额加权值上期末
        ,COALESCE(DEPF.WAMOUNT_YS,0)                                                                                         AS WAMOUNT_YS         -- 当年累计发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LC,0)                                                                                         AS WAMOUNT_LC         -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D011','D012','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069') AND CLIENT_TYPE = '0')DEPF

    UNION ALL

    -- 合计 机构+个人
    SELECT
         DEPF.FIN_ORG_NO                                             AS FIN_ORG_NO
        ,DEPF.CCY                                                    AS CCY
        ,'HJ'                                                        AS DEPOSIT_TYPE      -- 产品类别
        ,'合计'                                                      AS TYPE_DESC          -- 产品类别描述
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)                              AS OCCUR_AMOUNT       -- 存款发生额
        ,COALESCE(DEPF.AMOUNT_LM,0)                                  AS AMOUNT_LM          -- 发生额上期末
        ,COALESCE(DEPF.AMOUNT_YS,0)                                  AS AMOUNT_YS          -- 当年累计发生额
        ,COALESCE(DEPF.ACTUAL_AMOUNT,0)-COALESCE(DEPF.AMOUNT_LC,0)   AS AMOUNT_GROW        -- 同比增长
        ,COALESCE(DEPF.WSUM_BAL,0)                                   AS WSUM_BAL           -- 发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LM,0)                                 AS WAMOUNT_LM         -- 发生额加权值上期末
        ,COALESCE(DEPF.WAMOUNT_YS,0)                                 AS WAMOUNT_YS         -- 当年累计发生额加权值
        ,COALESCE(DEPF.WAMOUNT_LC,0)                                 AS WAMOUNT_LC         -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DEPOSIT_TYPE IN('D013','D014','D011','D012','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069'))DEPF
    )XT
   -- 与地区表最细级关联
   LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON XT.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
   LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
   GROUP BY   AREA.AREA_CODE_4
             ,XT.CCY
             ,XT.DEPOSIT_TYPE
             ,XT.TYPE_DESC ;